package com.neusoft.elm.dao.impl;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.FoodDao;
import com.neusoft.elm.po.Food;
import com.neusoft.elm.util.DBUtil;

public class FoodDaoImpl implements FoodDao{
	
	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	
	@Override
	public List<Food> listFoodByBusinessId(Integer businessId){
		List<Food> list = new ArrayList<>();
	    String sql = "select * from food where businessId=?";
	    	
	    	try {
	    		con = DBUtil.getConnection();
				pst = con.prepareStatement(sql);
				pst.setInt(1,businessId);
		    	rs = pst.executeQuery();
		    	while(rs.next()) {
		    	    Food food =new Food();
		    	    food.setFoodId(rs.getInt("foodId"));
		    	    food.setFoodName(rs.getString("foodName"));
		    	    food.setFoodExplain(rs.getString("foodExplain"));
		    	    BigDecimal foodPrice = new BigDecimal(rs.getDouble("foodPrice")).setScale(2, RoundingMode.HALF_UP);
		    	    if (rs.wasNull()) {
		    	        food.setFoodPrice(null);
		    	    } else {
		    	        food.setFoodPrice(foodPrice);
		    	    }
		    	    food.setBusinessId(rs.getInt("businessId"));
		    	    list.add(food);
		    	}
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(rs, pst, con);
			}
	    	return list;	
	}
	
	@Override
	public int saveFood(Food food) {
		int result = 0;
		int foodId = 0;
		String getMaxIdSql = "SELECT MAX(foodId) FROM food";
	    String insertFoodSql = "INSERT INTO food (foodId, foodName, foodExplain, foodPrice, businessId) VALUES (?, ?, ?, ?, ?)";    	
	    try {
	    	con = DBUtil.getConnection();
			pst = con.prepareStatement(getMaxIdSql);
			rs = pst.executeQuery();
			if (rs.next()) {
		         int maxId = rs.getInt(1);
		         foodId = maxId + 1;
		    }
				
			if (foodId != 0) {
		           pst = con.prepareStatement(insertFoodSql);
		           pst.setInt(1, foodId);
		           pst.setString(2, food.getFoodName());
		           pst.setString(3, food.getFoodExplain());
		           pst.setDouble(4, food.getFoodPrice());
		           pst.setInt(5, food.getBusinessId());		           
		           result = pst.executeUpdate();
		      }
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(null, pst, con);
			}
	    	return result;
		
	}
	
	@Override
	public Food getFoodById(Integer foodId) {
		Food food = null;
	    String sql = "select * from food where foodId=?";
	    	
	    	try {
	    		con = DBUtil.getConnection();
				pst = con.prepareStatement(sql);
				pst.setInt(1,foodId);
		    	rs = pst.executeQuery();
		    	while(rs.next()) {
		    	    food =new Food();
		    	    food.setFoodId(rs.getInt("foodId"));
		    	    food.setFoodName(rs.getString("foodName"));
		    	    food.setFoodExplain(rs.getString("foodExplain"));
		    	    BigDecimal foodPrice = new BigDecimal(rs.getDouble("foodPrice")).setScale(2, RoundingMode.HALF_UP);
		    	    if (rs.wasNull()) {
		    	        food.setFoodPrice(null);
		    	    } else {
		    	        food.setFoodPrice(foodPrice);
		    	    }
		    	    food.setBusinessId(rs.getInt("businessId"));		    	  
		    	}
	    	}catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DBUtil.close(rs, pst, con);
			}
	    	return food;
		
	}
	
	@Override
	public int updateFood(Food food) {
		int result = 0;
	    String sql = "update food set foodName=?,foodExplain=?,foodPrice=? where foodId=? ";	    	
	    try {
	    	con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1,food.getFoodName());
			pst.setString(2,food.getFoodExplain());
			pst.setDouble(3,food.getFoodPrice());
			pst.setInt(4,food.getFoodId());
			result = pst.executeUpdate();
	    }catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(null, pst, con);
		}
	    return result;
	}
	
	@Override
	public int removeFood(Integer foodId) {
		int result = 0;
		String deleteFoodSql = "DELETE FROM food WHERE foodId = ?";
	    String updateFoodSql = "UPDATE food SET foodId = foodId - 1 WHERE foodId > ?";
	    try {
	    	con = DBUtil.getConnection();
	    	
	    	pst = con.prepareStatement(deleteFoodSql);
	        pst.setInt(1, foodId);
	        result = pst.executeUpdate();
	        
	        pst = con.prepareStatement(updateFoodSql);
	        pst.setInt(1, foodId);
	        pst.executeUpdate();
	        
	    }catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(null, pst, con);
		}
	    return result;
	}
	
	 @Override
	 public int countFoodByBusinessId(Integer businessId) {
	        List<Food> foodList = getFoodsByBusinessId(businessId);
	        return foodList.size();
	    }
	 
	 
	 public List<Food> getFoodsByBusinessId(Integer businessId) {
		 List<Food> foodList = new ArrayList<>();

		    String sql = "SELECT * FROM food WHERE businessId = ?";
		    try {
		        con = DBUtil.getConnection();
		        pst = con.prepareStatement(sql);
		        pst.setInt(1, businessId);

		        rs = pst.executeQuery();
		        while (rs.next()) {
		            Food food = new Food();
		            food.setFoodId(rs.getInt("foodId"));
		            food.setFoodName(rs.getString("foodName"));
		            food.setFoodExplain(rs.getString("foodExplain"));
		            BigDecimal foodPrice = new BigDecimal(rs.getDouble("foodPrice")).setScale(2, RoundingMode.HALF_UP);
		            if (rs.wasNull()) {
		                food.setFoodPrice(null);
		            } else {
		                food.setFoodPrice(foodPrice);
		            }
		            food.setBusinessId(rs.getInt("businessId"));

		            foodList.add(food);
		        }
		    } catch (SQLException e) {
		        e.printStackTrace();
		    } finally {
		        DBUtil.close(rs, pst, con);
		    }

		    return foodList;
		}
      
	
}
